package com.cnd.scm.code.generator.provider;


import com.cnd.scm.code.generator.pojo.ColumnSchema;
import com.cnd.scm.code.generator.pojo.PrimaryKeySchema;
import com.cnd.scm.code.generator.pojo.TableSchema;
import com.cnd.scm.common.utils.CUtils;
import com.cnd.scm.jdbcprocessor.constant.CommandType;
import com.cnd.scm.jdbcprocessor.constant.DataBaseType;
import com.cnd.scm.jdbcprocessor.helper.SqlHelper;
import com.cnd.scm.orm.PhoenixRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
public class SqlSchemaProvider implements IDbSchemaProvider {

	@Autowired
	private PhoenixRepository phoenixRepository;

	@Autowired
	@Qualifier("sqlHelper")
	private SqlHelper sqlHelper;
	
	@Autowired
	private TemplateMethodForGetDataType getDataTypeTemplateMethod;
	
	@Autowired
	private TemplateMethodForUnderlineToCamel underlineToCamelTemplateMethod;


	private String databaseName = "";

    public DataBaseType dataBaseType(){
        return DataBaseType.SQLSERVER;
    }

		
	@Override
	public String getDatabaseName() {
		if (databaseName.equals("")) {
			try {
				List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text," SELECT DB_NAME() AS DATABASE_NAME ");
				Iterator iteratorRows = result.iterator();
				while (iteratorRows.hasNext()) {
					Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
					databaseName = CUtils.getMapValueByKeyIgnoreCase(map, "DATABASE_NAME").toString();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return databaseName;
	}

	@Override
	public List<TableSchema> getTables() {
		String sql = "SELECT OBJECT_NAME(O.ID) AS TABLE_NAME,USER_NAME(O.UID) AS OWNER,O.CRDATE AS CREATE_TIME,ISNULL(P.VALUE,'') AS TABLE_COMMENT \n" +
					 "FROM SYSOBJECTS O \n" +
					 "LEFT JOIN DBO.SYSPROPERTIES P ON O.ID=P.ID\n" +
					 "WHERE O.TYPE = N'U' OR O.TYPE = N'V' \n" +
					 "ORDER BY USER_NAME(O.UID), OBJECT_NAME(O.ID),O.TYPE" ;
		List<TableSchema> tableList = new ArrayList<>();
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text,sql);
                 
            Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				tableList.add((TableSchema)phoenixRepository.fillEntity(map, TableSchema.class, ""));
			}		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return tableList;
	}

	@Override
	public List<ColumnSchema> getTableColumns(String tableName) {
		String sql = String.format(
				"SELECT \r\n" + 
				"	CLMNS.[NAME] AS COLUMN_NAME,\r\n" + 
				"	CAST(CLMNS.ISNULLABLE AS BIT) AS IS_NULLABLE,\r\n" + 
				"	USRT.[NAME] AS DATA_TYPE,\r\n" + 
				"	CAST(CASE WHEN BASET.[NAME] IN (N'CHAR', N'VARCHAR', N'BINARY', N'VARBINARY', N'NCHAR', N'NVARCHAR') THEN CLMNS.PREC ELSE CLMNS.LENGTH END AS INT) AS [LENGTH],\r\n" + 
				"	EXT.[VALUE] AS COLUMM_COMMENT,	\r\n" + 
				"	TBL.[NAME] AS TABLE_NAME\r\n" + 
				"FROM DBO.SYSOBJECTS AS TBL WITH (NOLOCK)\r\n" + 
				"	INNER JOIN DBO.SYSCOLUMNS AS CLMNS WITH (NOLOCK) ON CLMNS.ID=TBL.ID\r\n" + 
				"	LEFT JOIN DBO.SYSTYPES AS USRT WITH (NOLOCK) ON USRT.XUSERTYPE = CLMNS.XUSERTYPE\r\n" + 
				"	LEFT JOIN DBO.SYSTYPES AS BASET WITH (NOLOCK) ON BASET.XUSERTYPE = CLMNS.XTYPE AND BASET.XUSERTYPE = BASET.XTYPE\r\n" + 
				"	LEFT JOIN SYS.EXTENDED_PROPERTIES AS EXT ON (CLMNS.ID=EXT.MAJOR_ID AND CLMNS.COLID =EXT.MINOR_ID AND EXT.NAME= 'MS_DESCRIPTION' ) \r\n" + 
				"WHERE (TBL.[TYPE] = 'U' OR TBL.[TYPE] = 'V') AND TBL.[NAME]='%s' \r\n" + 
				"ORDER BY TBL.[NAME], CLMNS.COLORDER",
				tableName);
		System.out.println(sql);
		List<ColumnSchema> colList = new ArrayList<>();
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text, sql);
			Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				colList.add((ColumnSchema)phoenixRepository.fillEntity(map, ColumnSchema.class, ""));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return colList;
	}

	@Override
	public List<PrimaryKeySchema> getTablePrimaryKey(String tableName) {
		List<PrimaryKeySchema> pkList = new ArrayList<>();
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.StoredProcedure, "SP_PKEYS",tableName);
			Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				pkList.add((PrimaryKeySchema) phoenixRepository.fillEntity(map, PrimaryKeySchema.class, ""));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		if (pkList.size() < 1) {// 如果没有定义主键就指定该表第一个字段为主键
			List<ColumnSchema> colList = getTableColumns(tableName);
			pkList.add(new PrimaryKeySchema(colList.get(0).getColumnName(), colList.get(0).getTableName()));
		}

		return pkList;
	}

	@Override
	public String getEntityPrimaryKeyProperties(String tableName) {
		return CUtils.underlineToCamel(getTablePrimaryKey(tableName).get(0).getColumnName().toLowerCase());
	}

	@Override
	public String getDataType(String type) {
		String t = type.toLowerCase();
		switch (t) {
		case "nchar":
		case "varchar":
		case "varchar(max)":
		case "text":
		case "nvarchar":
		case "nvarchar(max)":
		case "ntext":
		case "uniqueidentifier":
		case "xml":			
			return "String";
			
		case "bigint":
			return "Long";
			
		case "timestamp":
		case "binary":
		case "image":
		case "varbinary(max)":
			return "byte[]";
			
		case "float":	
			return "Double";			
		case "int":	
			return "Integer";			
		case "bit":	
			return "Boolean";			
		case "numeric":	
		case "decimal":	
			return "java.math.BigDecimal";			
		case "real":	
			return "Float";	
			
		case "smallint":
		case "tinyint":
			return "short";	
			
		case "date":
		case "datetime":
		case "datetime2":
		case "smalldatetime":
			return "Date";
				
		default:
			return "Object";
		}
	}

	/**
	 * 准备好渲染到模板上的数据
	 * @param tableName 数据库表名称
	 * @return
	 */
	@Override
	public Map<String,Object> prepareDataModel(String tableName){
		Map<String, Object> dataModel = new LinkedHashMap<String, Object>();
		dataModel.put("sourceTable", tableName);
		dataModel.put("columns", getTableColumns(tableName));
		dataModel.put("primaryKey",getTablePrimaryKey(tableName));
		dataModel.put("primaryKeyProperties", getEntityPrimaryKeyProperties(tableName));

		dataModel.put("underlineToCamel", underlineToCamelTemplateMethod);
		dataModel.put("getDataType", getDataTypeTemplateMethod);

		
		return dataModel;	
	}
	
}
